﻿CREATE PROCEDURE [util].[SendCDOMail]
@From VARCHAR (100), @To VARCHAR (100), @Subject VARCHAR (256)=' ', @Body VARCHAR (4000)=' ', @IsSMTPServerLocal BIT=0, @SMTPServerName VARCHAR (128)=NULL
AS
BEGIN
	SET NOCOUNT ON

	PRINT 'Sending CDO mail'
	/*********************************************************************	
	This stored procedure takes the parameters and sends an e-mail. 
	All the mail configurations are hard-coded in the stored procedure. 
	Comments are added to the stored procedure where necessary.
	References to the CDOSYS objects are at the following MSDN Web site:
	http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
	***********************************************************************/
	DECLARE @RC int
	SET @RC = 0
	
	DECLARE @iMsg int, @OAProc varchar(50), @hr int, @StepDesc varchar(100)

	SET @OAProc = 'sp_OACreate'
	
	SET @StepDesc = 'Creating the CDO.Message Object'
	EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @OAProc = 'sp_OASetProperty'

	SET @StepDesc = 'Configuring the Message Object: sendusing'
	-- This is to configure a remote SMTP server.
	-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
	DECLARE @SendUsingFlag char(1)
	SET @SendUsingFlag = '2'
	IF (@IsSMTPServerLocal = 1)
		SET @SendUsingFlag = '1'
	EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',@SendUsingFlag
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Configuring the Message Object: smtpserver'
	-- This is to configure the Server Name or IP address. 
	-- Replace MailServerName by the name or IP of your SMTP Server.
	IF(@SMTPServerName IS NULL)
		SET @SMTPServerName = config.GetInstanceSetting('SMTPServer')
	EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServerName
	IF (@hr <> 0) GOTO ERROR_HANDLER
	
	SET @StepDesc = 'Saving configurations to the message object'
	EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', NULL
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Setting e-mail parameters: To'
	EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Setting e-mail parameters: From'
	EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Setting e-mail parameters: Subject'
	EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @StepDesc = 'Setting e-mail parameters: Body'
	-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
	EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
	IF (@hr <> 0) GOTO ERROR_HANDLER

	SET @OAProc = 'sp_OAMethod'

	SET @StepDesc = 'Sending e-mail'
	EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
	IF (@hr <> 0) GOTO ERROR_HANDLER

	GOTO EXIT_PROC

ERROR_HANDLER:
	SET @RC = 1

	DECLARE @Source varchar(255), @Desc varchar(500)
	EXEC @hr = sp_OAGetErrorInfo NULL, @Source OUT, @Desc OUT
	IF (@hr <> 0)
		SELECT @Source = 'sp_OAGetErrorInfo', @Desc = 'Failed'

	INSERT INTO audit.CDOMailFailures (
		[DTS],[Spid],[From],[To],[Subject],[Body],[iMsg],[OAProc],[Hr],[Source],[Description],[StepDesc]
	)
	VALUES (
		GETDATE(), @@SPID, @From, @To, @Subject, @Body, @iMsg, @OAProc, @hr, @Source, @Desc, @StepDesc
	)

EXIT_PROC:
	IF (@iMsg IS NOT NULL)
	AND(@OAProc <> 'sp_OADestroy')
	BEGIN
		SET @OAProc = 'sp_OADestroy'
		SET @StepDesc = 'Destroying Message Object'
		EXEC @hr = sp_OADestroy @iMsg
		IF(@hr <> 0) GOTO ERROR_HANDLER
	END

	RETURN(@RC)
END